Imports DTO
Imports System.Data.OleDb
Public Class PhuTrachDAO
    Inherits AbstractDAO
    Public Function LayBang() As DataTable
        Dim dt As New DataTable
        Dim cn As OleDbConnection
        cn = ConnectionData()
        Dim strSQL As String
        strSQL = "select * from PhuTrach"
        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(strSQL, cn)
        da.Fill(dt)
        cn.Close()
        Return dt
    End Function
    Public Sub CapNhatBang(ByVal dt As DataTable)
        Dim cn As OleDbConnection
        cn = ConnectionData()
        Dim strSQL As String
        strSQL = "Select * from PhuTrach"
        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(strSQL, cn)
        Dim cb As New OleDbCommandBuilder(da)
        da.Update(dt)
        cn.Close()
    End Sub
    Public Function LayDanhSach() As IList
        Dim ds As New ArrayList()
        Dim cn As OleDbConnection
        cn = ConnectionData()
        Dim strSQL As String
        strSQL = "Select * from PhuTrach"
        Dim cmd As New OleDbCommand(strSQL, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader()
        While (dr.Read())
            Dim ptDTO As New PhuTrachDTO
            ptDTO.MaPhuTrach = dr("MaPhuTrach")
            ptDTO.MaGiaoVien = dr("MaGiaoVien")
            ptDTO.MaMonHoc = dr("MaMonHoc")
            ds.Add(ptDTO)
        End While
        dr.Close()
        cn.Close()
        Return ds
    End Function
    Public Function TiemKiemTheoMa(ByVal maphutrach As String) As PhuTrachDTO
        Dim ptDTO As New PhuTrachDTO
        Dim cn As OleDbConnection
        cn = ConnectionData()
        Dim strSQL As String
        strSQL = "Select * from PhuTrach Where MaPhuTrach = " & maphutrach
        Dim cmd As New OleDbCommand(strSQL, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader()
        While (dr.Read())
            ptDTO.MaPhuTrach = dr("MaPhuTrach")
            ptDTO.MaGiaoVien = dr("MaGiaoVien")
            ptDTO.MaMonHoc = dr("MaMonHoc")

        End While
        dr.Close()
        cn.Close()
        Return ptDTO
    End Function
    Public Sub Them(ByVal ptDTO As PhuTrachDTO)
        Dim cn As OleDbConnection
        cn = ConnectionData()
        Dim strSQL As String
        strSQL = "Insert into PhuTrach(MaPhuTrach,MaGiaoVien,MaMonHoc) value= " & _
        " ( '" & ptDTO.MaPhuTrach & "', '" & ptDTO.MaGiaoVien & "', '" & ptDTO.MaMonHoc & ")"
        Dim cmd As New OleDbCommand(strSQL, cn)
        cmd.ExecuteNonQuery()
        strSQL = "Select @@INDENTITY"
        cmd = New OleDbCommand(strSQL, cn)
        ptDTO.MaPhuTrach = cmd.ExecuteScalar()
        cn.Close()
    End Sub
    Public Sub XoaTheoMa(ByVal maphutrach As String)
        Dim cn As OleDbConnection
        cn = ConnectionData()
        Dim strSQL As String
        strSQL = "Delete from PhuTrach Where MaPhuTrach= " & maphutrach
        Dim cmd As New OleDbCommand(strSQL, cn)
        cmd.ExecuteNonQuery()
        cn.Close()

    End Sub
    Public Sub Sua(ByVal ptDTO As PhuTrachDTO)
        Dim cn As OleDbConnection
        cn = ConnectionData()
        Dim strSQL As String
        strSQL = "Update PhuTrach Set " & _
                " MaGiaoVien= '" & ptDTO.MaGiaoVien & _
                "' and MaMonHoc= '" & ptDTO.MaMonHoc & "Where MaPhuTrach= '" & ptDTO.MaPhuTrach & " '"
        Dim cmd As New OleDbCommand(strSQL, cn)
        cmd.ExecuteNonQuery()
        cn.Close()
    End Sub
End Class
